# -*- coding:utf-8 -*-
#  学员信息在线管理
import pymysql

class StuInfo:
    # 构造方法 数据库连接
    def __init__(self):
        self.db = pymysql.connect(host="localhost", user="root", password="", db="mydb", charset="utf8")
        # 使用cursor()创造一个游标对象
        self.cursor = self.db.cursor()

    # 初始化数据库和表，预存放学员信息
    def create(self):
        sql = "CREATE TABLE IF NOT EXISTS stu(id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL UNIQUE, age TINYINT UNSIGNED DEFAULT 20,classid VARCHAR(32) )"
        try:
            m = self.cursor.execute(sql)
            # 事务提交
            self.db.commit()
            return '成功操作条数：',m
        except Exception as err:
            # 事务回滚
            self.db.rollback()
            return 'SQL执行错误，原因：',err

    # 浏览学生信息
    def findAll(self):
        # 定义查询语句
        sql = "SELECT * FROM stu"
        try:
            # 使用execute()方法执行sql查询
            self.cursor.execute(sql)
            print('本次查询条数：', self.cursor.rowcount)
            # 使用fetchall()获取所有数据
            result = self.cursor.fetchall()
            if len(result) == 0:
                print("========== 没有学员信息可以输出！=============")
                return
            print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format("id", "name", "age", "classid"))
            print("-" * 40)
            for i in range(len(result)):
                print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format(result[i][0], result[i][1], result[i][2],
                                                                  result[i][3]))
        except Exception as err:
            return 'SQL执行错误，原因：',err

    # 学生信息添加
    def insert(self,data):
        # 定义查询语句
        sql = "INSERT INTO stu(name, age, classid) VALUES('%s', %d, '%s')" %(data)
        try:
            m = self.cursor.execute(sql)
            # 事务提交
            self.db.commit()
            return '成功操作条数：',m
        except Exception as err:
            # 事务回滚
            self.db.rollback()
            return 'SQL执行错误，原因：',err

    # 学生信息删除
    def delete(self,id):
        sql = "DELETE FROM stu WHERE id=%d" %(id)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return '成功操作条数：',self.cursor.rowcount
        except Exception as err:
            self.db.rollback()
            return 'SQL执行错误，原因：',err

    # 析构方法 关闭数据库连接
    def __del__(self):
        self.db.close()

# 实例化类
s = StuInfo()
# 建表，预存放学员信息
'''
print(s.create())
data1 = ('zhangsan', 20, 'python09')
data2 = ('lisi', 22, 'python10')
data3 = ('wangwu', 25, 'python10')
data4 = ('zhaoliu', 30, 'python09')
print(s.insert(data1))
s.insert(data2)
s.insert(data3)
s.insert(data4)
'''

while True:
    # 输出初始界面
    print("="*12,"学员管理系统","="*14)
    print("{0:1} {1:13} {2:15}".format(" ","1. 查看学员信息","2. 添加学员信息"))
    print("{0:1} {1:13} {2:15}".format(" ","3. 删除学员信息","4. 退出系统"))
    print("="*40)
    key = input("请输入对应的选择：")
    # 根据键盘值，判断并执行对应的操作
    if key == "1":
        print("="*12,"学员信息浏览","="*14)
        s.findAll()
        input("按回车键继续：")
    elif key == "2":
        print("="*12,"学员信息添加","="*14)
        name = input("请输入要添加的姓名：")
        age = int(input("请输入要添加的年龄："))
        classid = input("请输入要添加的班级号：")
        data = (name, age, classid)
        print(s.insert(data))
        s.findAll()
        input("按回车键继续：")
    elif key == "3":
        print("="*12,"学员信息删除","="*14)
        s.findAll()
        id = int(input("请输入你要删除的信息id号："))
        print(s.delete(id))
        s.findAll()
        input("按回车键继续：")
    elif key == "4":
        print("="*12,"再见","="*14)
        break
    else:
        print("======== 无效的键盘输入！ ==========")